var express = require('express');
var router = express.Router();
const { User, sequelize } = require('../../models');
const { Op } = require('sequelize');

// 统计用户性别，返回饼图格式数据
router.get('/sex', async function(req, res, next) {
  try {
    const maleCount = await User.count({ where: { sex: 0 }})
    const femaleCount = await User.count({ where: { sex: 1 }})
    const unknownCount = await User.count({ where: { sex: 2 }})
    const data = [
      { value: maleCount, name: '男性' },
      { value: femaleCount, name: '女性' },
      { value: unknownCount, name: '未知' }
    ]
    res.json({
      status: true,
      message: '查询用户性别成功',
      data: {
        data,
      }
    });
  } catch (error) {
    res.status(500).json({
      status: false,
      message: '查询用户性别失败',
      error: [error.message]
    });
  }
});

// 统计用户注册时间，返回折线图格式数据
router.get('/user', async function(req, res, next) {
  try {
    // 分组查询
    // 在 sequelize 中使用原生 SQL 查询
    const [results] = await sequelize.query("SELECT DATE_FORMAT(`createdAt`, '%Y-%m') AS `month`, COUNT(*) AS `value` FROM `Users` GROUP BY `month` ORDER BY `month` ASC;");

    const data = {
      months: [],
      values: []
    }
    results.forEach(item => {
      data.months.push(item.month);
      data.values.push(item.value);
    });

    res.json({
      status: true,
      message: '查询用户注册时间成功',
      data: {
        data,
      }
    });
  } catch (error) {
    res.status(500).json({
      status: false,
      message: '查询用户注册时间失败',
      error: [error.message]
    });
  }
});

module.exports = router;
